﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebPromenadebyenTabeller;
using System.Data.SqlClient;
using System.Configuration;

namespace PromArrangementDB
{
    public class PromGatewayArrangementer   
    {
        const String CONNECTION_STRING = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PromDatabase.mdf;Integrated Security=True;User Instance=True";

        public static List<Arrangementer> FindAlleArrangementer()
        {
            List<Arrangementer> nyListe = new List<Arrangementer>();

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            con.Open();
            

            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "SELECT * FROM ARRANGEMENTER";
            System.Data.IDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int ArrangementID = int.Parse(reader["arrangementID"].ToString());
                string Dato = reader["dato"].ToString();
                string Overskrift = reader["overskrift"].ToString();
                string Beskrivelse = reader["beskrivelse"].ToString();
                int Antaldeltagere = int.Parse(reader["antaldeltagere"].ToString());
                int Status = int.Parse(reader["status"].ToString());



                Arrangementer NyArrangementID = new Arrangementer(ArrangementID, Dato, Overskrift, Beskrivelse, Antaldeltagere, Status);

                nyListe.Add(NyArrangementID);

            }

            con.Close();
            return nyListe;
        }

        public static Arrangementer OpretArrangement(string dato, string overskrift, string beskrivelse, int antaldeltagere, int status)
        {


            Arrangementer nyArrangement = null;

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();

            command.CommandText = "INSERT INTO ARRANGEMENTER "
                                    + "(dato, overskrift, beskrivelse, antaldeltagere, status) VALUES " +
                                    "('" + dato + "', '" + overskrift + "', '" + beskrivelse + "', " + antaldeltagere + ", " + status + ")";


            int antalIndsat = command.ExecuteNonQuery();
            if (antalIndsat == 1)
            {
                command.CommandText = "SELECT @@identity AS lastinsertedkey";
                string slastkey = command.ExecuteScalar().ToString();
                int lastkey = int.Parse(slastkey);
                command.CommandText = "SELECT * FROM ARRANGEMENTER "
                                       + " WHERE (arrangementID=" + lastkey + ")";
                System.Data.IDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                  
                    int initArrangementID = int.Parse(reader["arrangementID"].ToString());
                    string initDato = reader["dato"].ToString();
                    string initOverskrift = reader["overskrift"].ToString();
                    string initBeskrivelse = reader["beskrivelse"].ToString();
                    int initAntaldeltagere = int.Parse(reader["antaldeltagere"].ToString());
                    int initStatus = int.Parse(reader["status"].ToString());


                    nyArrangement = new Arrangementer(initArrangementID, initDato, initOverskrift, initBeskrivelse, initAntaldeltagere, initStatus);
                }
            }
            con.Close();

            return nyArrangement;
        }

        public static void OpdaterArrangementer(int arrangementID, int lejlighedsID, string dato, string overskrift, string beskrivelse, int antaldeltagere)
        {

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "UPDATE TBLARRANGEMENT SET "
                                    + "ArrangementID=" + arrangementID + ", lejlighedsID=" + lejlighedsID + " " + "dato=" + dato + " " + "overskrift=" + overskrift + " "
                                    + "beskrivelse=" + beskrivelse + " " + "antaldeltagere=" + antaldeltagere
                                    + "WHERE ArrangementID=" + arrangementID;

            int antalOpdateret = command.ExecuteNonQuery();

            con.Close();
        }

        public static void TilmeldArrangement(int lejlighedsID, int antal, int arrangementsID)
        {

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();

            command.CommandText = "INSERT INTO DELTAGERE "
                                    + "(arrangementID, lejlighedsID, antaldeltagere) VALUES " +
                                    "(" + arrangementsID + ", " + lejlighedsID + ", " + antal + ")";
           
            int antalIndsat = command.ExecuteNonQuery();
            
            con.Close();
        }
    }
}